<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
	<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
	<title>security_audit: common_schema documentation</title>
	<meta name="description" content="security_audit: common_schema" />
	<meta name="keywords" content="security_audit: common_schema" />
	<link rel="stylesheet" type="text/css" href="css/style.css" />
</head>

<body>
	<div id="main">
		<div id="header">
			<h1>common_schema</h1> <strong>2.2</strong> documentation
			<div class="subtitle">DBA's framework for MySQL</div>
		</div>
		<div id="contentwrapper">
			<div id="content">
				<h2><a href="security_audit.html">security_audit</a></h2>	
<h3>NAME</h3>
security_audit(): Generate a server's security audit report.

<h3>TYPE</h3>
Procedure

<h3>DESCRIPTION</h3>
<p>
	Audit a server's security setup, including reviewing accounts and settings.
</p>
<p>
	This audit generates a human readable report with recommendations on
	actions to take so as to enhance server security. It does not take action
	nor modify any data.
</p>

<p>
	<i>security_audit()</i> reviews the following:
	<ul>
		<li>Non-local root accounts</li>
		<li>Anonymous users</li>
		<li>Accounts accessible by any host</li>
		<li>Password-less accounts</li>
		<li>Accounts sharing same password</li>
		<li>Non-root accounts with admin privileges</li>
		<li>Non-root accounts with global DDL privileges</li>
		<li>Non-root accounts with global DML privileges</li>
		<li>sql_mode</li>
		<li>Old passwords</li>
	</ul> 
</p>

<h3>SYNOPSIS</h3>
<p>
<blockquote><pre>security_audit() 
  READS SQL DATA</pre></blockquote>
</p>
<p>
  This procedure takes no input.
</p>

<h3>EXAMPLES</h3>
	<p>
		Audit a server:
	<blockquote><pre>mysql&gt; CALL security_audit();
+------------------------------------------------------------------------------+
| report                                                                       |
+------------------------------------------------------------------------------+
|                                                                              |
| Checking for non-local root accounts                                         |
| ====================================                                         |
| Recommendation: limit following root accounts to local machines              |
| &gt; rename 'root'@'central' to 'root'@'localhost'                              |
|                                                                              |
| Checking for anonymous users                                                 |
| ============================                                                 |
| OK                                                                           |
|                                                                              |
| Looking for accounts accessible from any host                                |
| =============================================                                |
| Recommendation: limit following accounts to specific hosts/subnet            |
| &gt; rename user 'apps'@'%' to 'apps'@'&lt;specific host&gt;'                         |
| &gt; rename user 'world_user'@'%' to 'world_user'@'&lt;specific host&gt;'             |
|                                                                              |
| Checking for accounts with empty passwords                                   |
| ==========================================                                   |
| Recommendation: set a decent password to these accounts.                     |
| &gt; set password for 'apps'@'%' = PASSWORD(...)                                |
| &gt; set password for 'world_user'@'localhost' = PASSWORD(...)                  |
| &gt; set password for 'wu'@'localhost' = PASSWORD(...)                          |
|                                                                              |
| Looking for accounts with identical (non empty) passwords                    |
| =========================================================                    |
| Different users should not share same password.                              |
| Recommendation: Change passwords for accounts listed below.                  |
|                                                                              |
| The following accounts share the same password:                              |
| 'temp'@'10.0.%'                                                              |
| 'temp'@'10.0.0.%'                                                            |
| 'gromit'@'localhost'                                                         |
|                                                                              |
| The following accounts share the same password:                              |
| 'replication'@'10.0.0.%'                                                     |
| 'shlomi'@'localhost'                                                         |
|                                                                              |
| The following accounts share the same password:                              |
| 'shlomi'@'127.0.0.1'                                                         |
| 'monitoring_user'@'localhost'                                                |
|                                                                              |
| Looking for (non-root) accounts with admin privileges                        |
| =====================================================                        |
| Normal users should not have admin privileges, such as                       |
| SUPER, SHUTDOWN, RELOAD, PROCESS, CREATE USER, REPLICATION CLIENT.           |
| Recommendation: limit privileges to following accounts.                      |
| &gt; GRANT &lt;non-admin-privileges&gt; ON *.* TO 'monitoring_user'@'localhost'       |
| &gt; GRANT &lt;non-admin-privileges&gt; ON *.* TO 'shlomi'@'localhost'                |
|                                                                              |
| Looking for (non-root) accounts with global DDL privileges                   |
| ==========================================================                   |
| OK                                                                           |
|                                                                              |
| Looking for (non-root) accounts with global DML privileges                   |
| ==========================================================                   |
| OK                                                                           |
|                                                                              |
| Testing sql_mode                                                             |
| ================                                                             |
| Server's sql_mode does not include NO_AUTO_CREATE_USER.                      |
| This means users can be created with empty passwords.                        |
| Recommendation: add NO_AUTO_CREATE_USER to sql_mode,                         |
| both in config file as well as dynamically.                                  |
| &gt; SET @@global.sql_mode := CONCAT(@@global.sql_mode, ',NO_AUTO_CREATE_USER') |
|                                                                              |
| Testing old_passwords                                                        |
| =====================                                                        |
| OK                                                                           |
|                                                                              |
| Checking for `test` database                                                 |
| ============================                                                 |
| `test` database has been found.                                              |
| `test` is a special database where any user can create, drop and manipulate  |
| table data. Recommendation: drop it                                          |
| &gt; DROP DATABASE `test`                                                       |
| ---                                                                          |
| Report generated on '2012-09-21 11:49:52                                     |
+------------------------------------------------------------------------------+
</pre></blockquote>
	</p>




<h3>ENVIRONMENT</h3>
MySQL 5.1 or newer

<h3>SEE ALSO</h3>
<a href="killall.html">killall</a>,
<a href="processlist_grantees.html">processlist_grantees</a>,
<a href="sql_accounts.html">sql_accounts</a>

<h3>AUTHOR</h3>
Shlomi Noach
				<br/>
			</div>
			<div id="sidebarwrapper">
				<div id="search">
					Search online documentation
					<form id="search_form" name="search_form" method="GET" 
						action="http://www.google.com/search" 
						onsubmit="document.forms['search_form']['q'].value = 'site:http://common-schema.googlecode.com/svn/trunk/common_schema/doc/html/ '+document.forms['search_form']['search_term'].value;">
						<input type="text" name="search_term" value=""/>
						<input type="hidden" name="q" value=""/>
						<input type="submit" value="go"/>						
					</form>
				</div>
				<div id="menu">
					<ul>
						<li><a title="Introduction" href="introduction.html">Introduction</a></li>
						<li><a title="Documentation" href="documentation.html">Documentation</a></li>
						<li><a title="Download" href="download.html">Download</a></li>
						<li><a title="Install" href="install.html">Install</a></li>
						<li><a title="Risks" href="risks.html">Risks</a></li>
					</ul>						
					<h3>QUERY SCRIPT</h3>
					<ul>
						<li><a title="QueryScript" href="query_script.html">QueryScript</a></li>
						<li><a title="Execution" href="query_script_execution.html">Execution</a></li>
						<li><a title="Flow control" href="query_script_flow_control.html">Flow control</a></li>
						<li><a title="Statements" href="query_script_statements.html">Statements</a></li>
						<li><a title="Expressions" href="query_script_expressions.html">Expressions</a></li>
						<li><a title="Variables" href="query_script_variables.html">Variables</a></li>
					</ul>						
					<h3>DEBUG</h3>
					<ul>
						<li><a title="rdebug" href="rdebug.html">rdebug</a></li>
						<li><a title="rdebug API" href="rdebug_api.html">rdebug API</a></li>
						<li><a title="rdebug workflow" href="rdebug_workflow.html">Workflow</a></li>
					</ul>						
					<h3>ROUTINES</h3>
					<ul>
						<li><a title="Execution &amp; flow control" href="execution_routines.html">Execution & flow control</a></li>
						<li><a title="General" href="general_routines.html">General</a></li>
						<li><a title="Process" href="process_routines.html">Process</a></li>
						<li><a title="Query analysis" href="query_analysis_routines.html">Query analysis</a></li>
						<li><a title="Schema analysis" href="schema_analysis_routines.html">Schema analysis</a></li>
						<li><a title="Security" href="security_routines.html">Security</a></li>
						<li><a title="Text" href="text_routines.html">Text</a></li>
						<li><a title="Time &amp; date" href="temporal_routines.html">Time & date</a></li>
						<li><a title="Charting" href="charting_routines.html">Charting</a></li>
					</ul>
					<h3>VIEWS</h3>
					<ul>
						<li><a title="Schema analysis" href="schema_analysis_views.html">Schema analysis</a></li>
						<li><a title="Data dimension" href="data_dimension_views.html">Data dimension</a></li>
						<li><a title="Process" href="process_views.html">Process</a></li>
						<li><a title="Security" href="security_views.html">Security</a></li>
						<li><a title="Monitoring" href="monitoring_views.html">Monitoring</a></li>
						<li><a title="InnoDB Plugin" href="innodb_plugin_views.html">InnoDB Plugin</a></li>
						<li><a title="Percona server" href="percona_server_views.html">Percona Server</a></li>
						<li><a title="TokuDB" href="tokudb_views.html">TokuDB</a></li>
					</ul>						
					<h3>DATA</h3>
					<ul>
						<li><a title="tables" href="tables.html">Tables</a></li>
						<li><a title="variables" href="variables.html">Variables</a></li>
					</ul>						
					<h3>META</h3>
					<ul>
						<li><a title="Help" href="help.html">help</a></li>
						<li><a title="Metadata" href="metadata.html">metadata</a></li>
						<li><a title="status" href="status.html">status</a></li>
					</ul>						
				</div>
			</div>	
			<div class="clear">&nbsp;</div>
			
			<div id="footnote" align="center">
				<a href="">common_schema</a> documentation
			</div>
		</div>
	</div>
</body>
</html>
